package com.xdb.sqlparser.util;

import java.util.ArrayList;
import java.util.List;

import com.xdb.exception.XDBRuntimeException;
import com.xdb.logging.Logger;
import com.xdb.logging.LogFactory;
import com.xdb.sqlparser.ast.SQLExpr;
import com.xdb.sqlparser.ast.SQLObject;
import com.xdb.sqlparser.ast.SQLStatement;
import com.xdb.sqlparser.ast.expr.SQLBinaryOpExpr;
import com.xdb.sqlparser.ast.expr.SQLBinaryOperator;
import com.xdb.sqlparser.ast.statement.SQLDeleteStatement;
import com.xdb.sqlparser.ast.statement.SQLSelectItem;
import com.xdb.sqlparser.ast.statement.SQLSelectOrderByItem;
import com.xdb.sqlparser.ast.statement.SQLSelectQuery;
import com.xdb.sqlparser.ast.statement.SQLSelectQueryBlock;
import com.xdb.sqlparser.ast.statement.SQLSelectStatement;
import com.xdb.sqlparser.ast.statement.SQLUpdateSetItem;
import com.xdb.sqlparser.ast.statement.SQLUpdateStatement;
import com.xdb.sqlparser.dialect.mysql.visitor.MySqlOutputVisitor;
import com.xdb.sqlparser.dialect.oracle.visitor.OracleOutputVisitor;
import com.xdb.sqlparser.dialect.oracle.visitor.OracleToMySqlOutputVisitor;
import com.xdb.sqlparser.dialect.sql99.parser.SQL99ExprParser;
import com.xdb.sqlparser.dialect.sql99.parser.SQL99StatementParser;
import com.xdb.sqlparser.exception.ParserException;
import com.xdb.sqlparser.parser.SQLToken;
import com.xdb.sqlparser.visitor.SQLAstOutputVisitor;
import com.xdb.util.JDBCConstants;
import com.xdb.util.JdbcUtils;
import com.xdb.util.StringUtils;

public class SQLUtils {

	private final static Logger LOG = LogFactory.getLog(SQLUtils.class);

	public static String toSQLString(SQLObject sqlObject, String dbType) {
		if (JdbcUtils.MYSQL.equals(dbType) || JdbcUtils.H2.equals(dbType)) {
			return toMySqlString(sqlObject);
		} else {
			return toOracleString(sqlObject);
		}

	}

	public static String toSQLString(SQLObject sqlObject) {
		StringBuilder out = new StringBuilder();
		sqlObject.accept(new SQLAstOutputVisitor(out));

		String sql = out.toString();
		return sql;
	}

	public static String toMySqlString(SQLObject sqlObject) {
		StringBuilder out = new StringBuilder();
		sqlObject.accept(new MySqlOutputVisitor(out));

		String sql = out.toString();
		return sql;
	}

	public static SQLExpr toMySqlExpr(String sql) {
		return toSQLExpr(sql, JdbcUtils.MYSQL);
	}

	public static String formatMySql(String sql) {
		return format(sql, JdbcUtils.MYSQL);
	}

	public static String formatOracle(String sql) {
		return format(sql, JdbcUtils.ORACLE);
	}

	public static String toOracleString(SQLObject sqlObject) {
		StringBuilder out = new StringBuilder();
		sqlObject.accept(new OracleOutputVisitor(out, false));

		String sql = out.toString();
		return sql;
	}

	public static SQLExpr toSQLExpr(String sql, String dbType) {
		SQL99ExprParser parser = SQLParserUtils.createExprParser(sql, dbType);
		SQLExpr expr = parser.expr();

		if (parser.getLexer().token() != SQLToken.EOF) {
			throw new ParserException("illegal sql expr : " + sql);
		}

		return expr;
	}

	public static SQLSelectOrderByItem toOrderByItem(String sql, String dbType) {
		SQL99ExprParser parser = SQLParserUtils.createExprParser(sql, dbType);
		SQLSelectOrderByItem orderByItem = parser.parseSelectOrderByItem();

		if (parser.getLexer().token() != SQLToken.EOF) {
			throw new ParserException("illegal sql expr : " + sql);
		}

		return orderByItem;
	}

	public static SQLUpdateSetItem toUpdateSetItem(String sql, String dbType) {
		SQL99ExprParser parser = SQLParserUtils.createExprParser(sql, dbType);
		SQLUpdateSetItem updateSetItem = parser.parseUpdateSetItem();

		if (parser.getLexer().token() != SQLToken.EOF) {
			throw new ParserException("illegal sql expr : " + sql);
		}

		return updateSetItem;
	}

	public static SQLSelectItem toSelectItem(String sql, String dbType) {
		SQL99ExprParser parser = SQLParserUtils.createExprParser(sql, dbType);
		SQLSelectItem selectItem = parser.parseSelectItem();

		if (parser.getLexer().token() != SQLToken.EOF) {
			throw new ParserException("illegal sql expr : " + sql);
		}

		return selectItem;
	}

	public static List<SQLStatement> toStatementList(String sql, String dbType) {
		SQL99StatementParser parser = SQLParserUtils.createSQLStatementParser(sql, dbType);
		return parser.parseStatementList();
	}

	public static SQLExpr toSQLExpr(String sql) {
		return toSQLExpr(sql, null);
	}

	public static String format(String sql, String dbType) {
		return format(sql, dbType, null);
	}

	public static String format(String sql, String dbType, List<Object> parameters) {
		try {
			List<SQLStatement> statementList = toStatementList(sql, dbType);

			return toSQLString(statementList, dbType, parameters);
		} catch (ParserException ex) {
			LOG.warn("format error", ex);
			return sql;
		}
	}

	public static String toSQLString(List<SQLStatement> statementList, String dbType) {
		return toSQLString(statementList, dbType, null);
	}

	public static String toSQLString(List<SQLStatement> statementList, String dbType, List<Object> parameters) {
		StringBuilder out = new StringBuilder();
		SQLAstOutputVisitor visitor = createFormatOutputVisitor(out, statementList, dbType);
		if (parameters != null) {
			visitor.setParameters(parameters);
		}

		for (int i = 0; i < statementList.size(); i++) {
			if (i > 0) {
				out.append(";\n");
			}
			statementList.get(i).accept(visitor);
		}

		return out.toString();
	}

	public static SQLAstOutputVisitor createFormatOutputVisitor(Appendable out, List<SQLStatement> statementList, String dbType) {
		if (JdbcUtils.ORACLE.equals(dbType)) {
			if (statementList.size() == 1) {
				return new OracleOutputVisitor(out, false);
			} else {
				return new OracleOutputVisitor(out, true);
			}
		}

		if (JdbcUtils.MYSQL.equals(dbType) || JdbcUtils.H2.equals(dbType)) {
			return new MySqlOutputVisitor(out);
		}

		return new SQLAstOutputVisitor(out);
	}

	public static List<SQLStatement> parseStatements(String sql, String dbType) {
		SQL99StatementParser parser = SQLParserUtils.createSQLStatementParser(sql, dbType);
		List<SQLStatement> stmtList = parser.parseStatementList();
		if (parser.getLexer().token() != SQLToken.EOF) {
			throw new XDBRuntimeException("syntax error : " + sql);
		}
		return stmtList;
	}

	/**
	 * @author owenludong.lud
	 * @param columnName
	 * @param tableAlias
	 * @param pattern
	 *            if pattern is null,it will be set {%Y-%m-%d %H:%i:%s} as mysql
	 *            default value and set {yyyy-mm-dd hh24:mi:ss} as oracle
	 *            default value
	 * @param dbType
	 *            {@link JDBCConstants} if dbType is null ,it will be set the
	 *            mysql as a default value
	 */
	public static String buildToDate(String columnName, String tableAlias, String pattern, String dbType) {
		StringBuilder sql = new StringBuilder();
		if (StringUtils.isEmpty(columnName))
			return "";
		if (StringUtils.isEmpty(dbType))
			dbType = JDBCConstants.MYSQL;
		String formatMethod = "";
		if (JDBCConstants.MYSQL.equalsIgnoreCase(dbType)) {
			formatMethod = "STR_TO_DATE";
			if (StringUtils.isEmpty(pattern))
				pattern = "%Y-%m-%d %H:%i:%s";
		} else if (JDBCConstants.ORACLE.equalsIgnoreCase(dbType)) {
			formatMethod = "TO_DATE";
			if (StringUtils.isEmpty(pattern))
				pattern = "yyyy-mm-dd hh24:mi:ss";
		} else {
			return "";
			// expand date's handle method for other database
		}
		sql.append(formatMethod).append("(");
		if (!StringUtils.isEmpty(tableAlias))
			sql.append(tableAlias).append(".");
		sql.append(columnName).append(",");
		sql.append("'");
		sql.append(pattern);
		sql.append("')");
		return sql.toString();
	}

	public static List<SQLExpr> split(SQLBinaryOpExpr x) {
		List<SQLExpr> groupList = new ArrayList<SQLExpr>();
		groupList.add(x.getRight());

		SQLExpr left = x.getLeft();
		for (;;) {
			if (left instanceof SQLBinaryOpExpr && ((SQLBinaryOpExpr) left).getOperator() == x.getOperator()) {
				SQLBinaryOpExpr binaryLeft = (SQLBinaryOpExpr) left;
				groupList.add(binaryLeft.getRight());
				left = binaryLeft.getLeft();
			} else {
				groupList.add(left);
				break;
			}
		}
		return groupList;
	}

	public static String translateOracleToMySql(String sql) {
		List<SQLStatement> stmtList = toStatementList(sql, JDBCConstants.ORACLE);

		StringBuilder out = new StringBuilder();
		OracleToMySqlOutputVisitor visitor = new OracleToMySqlOutputVisitor(out, false);
		for (int i = 0; i < stmtList.size(); ++i) {
			stmtList.get(i).accept(visitor);
		}

		String mysqlSql = out.toString();
		return mysqlSql;

	}

	public static String addCondition(String sql, String condition, String dbType) {
		String result = addCondition(sql, condition, SQLBinaryOperator.BooleanAnd, false, dbType);
		return result;
	}

	public static String addCondition(String sql, String condition, SQLBinaryOperator op, boolean left, String dbType) {
		if (sql == null) {
			throw new IllegalArgumentException("sql is null");
		}

		if (condition == null) {
			return sql;
		}

		if (op == null) {
			op = SQLBinaryOperator.BooleanAnd;
		}

		if (op != SQLBinaryOperator.BooleanAnd //
				&& op != SQLBinaryOperator.BooleanOr) {
			throw new IllegalArgumentException("add condition not support : " + op);
		}

		List<SQLStatement> stmtList = parseStatements(sql, dbType);

		if (stmtList.size() == 0) {
			throw new IllegalArgumentException("not support empty-statement :" + sql);
		}

		if (stmtList.size() > 1) {
			throw new IllegalArgumentException("not support multi-statement :" + sql);
		}

		SQLStatement stmt = stmtList.get(0);

		SQLExpr conditionExpr = toSQLExpr(condition, dbType);

		addCondition(stmt, op, conditionExpr, left);

		return toSQLString(stmt, dbType);
	}

	public static void addCondition(SQLStatement stmt, SQLBinaryOperator op, SQLExpr condition, boolean left) {
		if (stmt instanceof SQLSelectStatement) {
			SQLSelectQuery query = ((SQLSelectStatement) stmt).getSelect().getQuery();
			if (query instanceof SQLSelectQueryBlock) {
				SQLSelectQueryBlock queryBlock = (SQLSelectQueryBlock) query;
				SQLExpr newCondition = buildCondition(op, condition, left, queryBlock.getWhere());
				queryBlock.setWhere(newCondition);
			} else {
				throw new IllegalArgumentException("add condition not support " + stmt.getClass().getName());
			}

			return;
		}

		if (stmt instanceof SQLDeleteStatement) {
			SQLDeleteStatement delete = (SQLDeleteStatement) stmt;

			SQLExpr newCondition = buildCondition(op, condition, left, delete.getWhere());
			delete.setWhere(newCondition);

			return;
		}

		if (stmt instanceof SQLUpdateStatement) {
			SQLUpdateStatement update = (SQLUpdateStatement) stmt;

			SQLExpr newCondition = buildCondition(op, condition, left, update.getWhere());
			update.setWhere(newCondition);

			return;
		}

		throw new IllegalArgumentException("add condition not support " + stmt.getClass().getName());
	}

	public static SQLExpr buildCondition(SQLBinaryOperator op, SQLExpr condition, boolean left, SQLExpr where) {
		if (where == null) {
			return condition;
		}

		SQLBinaryOpExpr newCondition;
		if (left) {
			newCondition = new SQLBinaryOpExpr(condition, op, where);
		} else {
			newCondition = new SQLBinaryOpExpr(where, op, condition);
		}
		return newCondition;
	}

	public static String addSelectItem(String selectSql, String expr, String alias, String dbType) {
		return addSelectItem(selectSql, expr, alias, false, dbType);
	}

	public static String addSelectItem(String selectSql, String expr, String alias, boolean first, String dbType) {
		List<SQLStatement> stmtList = parseStatements(selectSql, dbType);

		if (stmtList.size() == 0) {
			throw new IllegalArgumentException("not support empty-statement :" + selectSql);
		}

		if (stmtList.size() > 1) {
			throw new IllegalArgumentException("not support multi-statement :" + selectSql);
		}

		SQLStatement stmt = stmtList.get(0);

		SQLExpr columnExpr = toSQLExpr(expr, dbType);

		addSelectItem(stmt, columnExpr, alias, first);

		return toSQLString(stmt, dbType);
	}

	public static void addSelectItem(SQLStatement stmt, SQLExpr expr, String alias, boolean first) {
		if (expr == null) {
			return;
		}

		if (stmt instanceof SQLSelectStatement) {
			SQLSelectQuery query = ((SQLSelectStatement) stmt).getSelect().getQuery();
			if (query instanceof SQLSelectQueryBlock) {
				SQLSelectQueryBlock queryBlock = (SQLSelectQueryBlock) query;
				addSelectItem(queryBlock, expr, alias, first);
			} else {
				throw new IllegalArgumentException("add condition not support " + stmt.getClass().getName());
			}

			return;
		}

		throw new IllegalArgumentException("add selectItem not support " + stmt.getClass().getName());
	}

	public static void addSelectItem(SQLSelectQueryBlock queryBlock, SQLExpr expr, String alias, boolean first) {
		SQLSelectItem selectItem = new SQLSelectItem(expr, alias);
		queryBlock.getSelectList().add(selectItem);
		selectItem.setParent(selectItem);
	}
}
